﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.IO;
using CoppyDatabase.Commons;

namespace CoppyDatabase
{
    public partial class frmCopyData : Form
    {

        public frmCopyData()
        {
            InitializeComponent();
        }

        private SqlConnection _SqlCon;
        public SqlConnection gsConnection_Sql
        {
            get { return _SqlCon; }
            set { _SqlCon = value; }
        }

        private MySqlConnection _MySqlCon;
        public MySqlConnection gsConnection_MySql
        {
            get { return _MySqlCon; }
            set { _MySqlCon = value; }
        }

        private string _ConvertType;
        public string gs_ConvertType
        {
            get { return _ConvertType; }
            set { _ConvertType = value; }
        }

        string sql_SeverName = "", sql_DataBaseName = "", sql_UserName = "", sql_Pass = "";

        #region Khai báo biến SQL
        private SqlConnection sql_con;
        private SqlCommand sql_cmd;
        private SqlDataAdapter sql_Ad;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();
        #endregion

        #region Khai báo biến MySQL
        private MySqlConnection MySql_con;
        private MySqlCommand MySql_cmd;
        private MySqlDataAdapter MySql_Ad;
        private DataSet MySql_DS = new DataSet();
        private DataTable MySql_DT = new DataTable();
        #endregion
        private void frmCopyData_Load(object sender, EventArgs e)
        {
            try
            {
                sql_con = gsConnection_Sql;
                MySql_con = gsConnection_MySql;

                //get all table
                string selectTablesName_sql = "SELECT TABLE_NAME FROM information_schema.tables";
                DataTable _listTable = new DataTable();
                _listTable = clsExecuteQuery.SelectSql_ReturnTable(sql_con, selectTablesName_sql);
                gridTable.DataSource = _listTable;
               // gridTable. ;


                ////    //obj.Close();


                ////    #region Lấy Data
                ////    //sql_con.Open();
                ////    //sql_cmd = sql_con.CreateCommand();
                ////    //string selectTablesName_sql = "SELECT TABLE_NAME FROM information_schema.tables";
                ////    //DataTable dtTablesName = new DataTable();
                ////    //sql_Ad = new SqlDataAdapter(selectTablesName_sql, sql_con);
                ////    //DS.Reset();
                ////    //sql_Ad.Fill(DS);
                ////    //dtTablesName = DS.Tables[0];
                ////    #endregion


                ////#region Connection SQL
                ////    SetConnection();
                ////    sql_con.Open();
                ////    sql_cmd = sql_con.CreateCommand();
                ////    string selectTablesName_sql = "SELECT TABLE_NAME FROM information_schema.tables";
                ////    //SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbo'
                ////   // string selectTablesName_sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='hong86_spotvietnamdb'";
                ////    #region Add value for combobox TablesName
                ////    DataTable dtTablesName = new DataTable();
                ////    sql_Ad = new SqlDataAdapter(selectTablesName_sql, sql_con);
                ////    DS.Reset();
                ////    sql_Ad.Fill(DS);
                ////    dtTablesName = DS.Tables[0];
                ////    lkSql_tablesName.DataSource = dtTablesName;
                ////    lkSql_tablesName.DisplayMember = "TABLE_NAME";
                ////    lkSql_tablesName.ValueMember = "TABLE_NAME";
                ////    #endregion
                ////    sql_con.Close();
                ////#endregion
                ////#region Conection MySql
                ////    try
                ////    {
                ////         SetMySqlConnection();
                ////    }
                ////    catch (Exception ex)
                ////    {
                ////    }
                ////#endregion

            }
            catch (Exception ex)
            {
            }
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            StreamReader reader = File.OpenText(Common.GetPath_SqlConnection()); ;
            try
            {
                //#region read data file .txt
                ////string strAllFile = reader.ReadToEnd(); // doc toan bo data trong file.txt
                ////string[] arrLines = strAllFile.Split(new string[] { ";", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries); // split ky tu [\r\n] va convert sang 1 Mang
                ////if (strAllFile != null)
                ////{
                ////    SeverName = arrLines[0]; // dong 1
                ////    DataBaseName = arrLines[1]; // dong 2
                ////    UserName = arrLines[2]; // dong 3
                ////    Pass = arrLines[3]; // dong 4
                ////}
                ////reader.Close();
                ////reader.Dispose();
                //#endregion

                //string tableName = lkSql_tablesName.Text.Trim();


                //if (!Common.IsNullOrEmpty(tableName))
                //{
                //    SetConnection();
                //    sql_con.Open();
                //    sql_cmd = sql_con.CreateCommand();
                //    string SelectTableName = "select * from " + tableName;
                //    sql_Ad = new SqlDataAdapter(SelectTableName, sql_con);
                //    DS.Reset();
                //    sql_Ad.Fill(DS);
                //    DT = DS.Tables[0];
                //    #region Insert Data
                //    foreach (DataRow dr in DT.Rows)
                //    {
                //        int Paramater = 0;
                //        string strValue = "";
                //        string fullValues = "";
                //        foreach (var item in dr.ItemArray) // Loop over the items.
                //        {
                //            Paramater = Paramater + 1;

                //            if (Common.ParseString(item).Length > 10)
                //            {
                //                strValue += "'" + item + "',";
                //            }
                //            else
                //            {
                //                if (Common.ParseInt(item) == 0)
                //                {
                //                    strValue += "'" + item + "',";
                //                }
                //                else
                //                {
                //                    strValue += item + ",";
                //                }
                //            }
                //        }
                //        fullValues = strValue.Substring(0, strValue.Length - 1);
                //        string strInsert = "INSERT INTO " + tableName + " VALUES (" + fullValues + ")";
                //        ExecuteMySqlQuery(strInsert);
                //    }
                //    #endregion
                //}
            }
            catch (Exception ex)
            {
                reader.Close();
                reader.Dispose();

                MessageBox.Show("Kết Nối DataBase Không Thành Công \n Vui Lòng Kiểm Tra Lại SeverName, DatabaseName, UserName, Password");
                // FormLogIn obj = new FormLogIn();
                // obj.Show();
            }
        }

        private void btnCreateTable_Click(object sender, EventArgs e)
        {
            string strTableError = "";
            try
            {
                if (gs_ConvertType == "Convert Sql to MySql")
                {

                    //get Relationship giua cac table
                    DataTable _RelationshipTable = new DataTable();
                    _RelationshipTable = clsExecuteQuery.getRelationship_Sql(sql_con);

                    //get all table
                    string selectTablesName_sql = "SELECT TABLE_NAME FROM information_schema.tables";
                    DataTable _listTable = new DataTable();
                    _listTable = clsExecuteQuery.SelectSql_ReturnTable(sql_con, selectTablesName_sql);
                    foreach (DataRow dr_listTable in _listTable.Rows)
                    {
                        //string strTableName = "M_MENU";
                        string strTableName = dr_listTable["TABLE_NAME"].ToString().Trim();
                        strTableError = strTableName;
                        #region Create table MySQL
                        //get Data
                        string strSelect = "Select * from " + strTableName;
                        DataTable _DataTableCurrent = new DataTable();
                        _DataTableCurrent = clsExecuteQuery.SelectSql_ReturnTable(sql_con, strSelect);

                        // get thong tin cua bang
                        DataTable InformationTable = new DataTable();
                        InformationTable = clsExecuteQuery.InformationTable(sql_con, strTableName);
                        //get column khoa chinh
                        DataTable TableColumn_PrimaryKey = new DataTable();
                        TableColumn_PrimaryKey = clsExecuteQuery.ColumnPrimaryKey(sql_con, strTableName);
                        //get cot tang tu dong
                        DataTable TableColumn_Indentity = new DataTable();
                        TableColumn_Indentity = clsExecuteQuery.ColumnIdentity(sql_con, strTableName);

                        
                        #region Create table
                        string strCreateTable = "Create table " + strTableName + " ( ";
                        foreach (DataRow dr in InformationTable.Rows)
                        {
                            string strColumnName = "`" + dr["COLUMN_NAME"].ToString() + "`";
                            string strValueDefault = dr["COLUMN_DEFAULT"].ToString();
                            string strDataType = string.Empty;
                            string strCanNull = dr["IS_NULLABLE"].ToString();

                            //get xem co cho null hay ko ?------------------------
                            if (strCanNull == "NO")
                                strCanNull = "Not Null";
                            else
                                strCanNull = string.Empty;
                            //Get maxlength------------------------
                            if (Common.IsNullOrEmpty(dr["CHARACTER_MAXIMUM_LENGTH"]))
                                strDataType = dr["DATA_TYPE"].ToString();
                            else
                            {
                                if (dr["DATA_TYPE"].ToString() == "ntext")
                                    strDataType = "text CHARACTER SET utf8 COLLATE utf8_general_ci";
                                else
                                {
                                    decimal _maxLength = 0;
                                    if (Common.ParseDecimal(dr["CHARACTER_MAXIMUM_LENGTH"].ToString()) < 0)
                                    {
                                        //_maxLength = 16384;
                                        strDataType = "text CHARACTER SET utf8 COLLATE utf8_general_ci";
                                    }
                                    else
                                    {
                                        _maxLength = Common.ParseDecimal(dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
                                        strDataType = dr["DATA_TYPE"].ToString() + "(" + _maxLength + ")";
                                    }

                                    
                                }
                            }
                            //get values default------------------------
                            if (!Common.IsNullOrEmpty(strValueDefault))
                            {
                                string _valueDefault = strValueDefault.Replace("((", "").Replace("))", "");
                                if (Common.CheckIsNumber(_valueDefault))
                                {
                                    strValueDefault = "Default " + _valueDefault;
                                }
                                else
                                {
                                    strValueDefault = " Default N'" + strValueDefault.Replace("((", "").Replace("))", "") + "'";
                                }
                            }
                            strCreateTable = strCreateTable + strColumnName + " " + strDataType + " " + strCanNull + " " + strValueDefault;

                            foreach (DataRow dr_Indentity in TableColumn_Indentity.Rows)
                            {
                                string intFirst = dr_Indentity["SEED_VALUE"].ToString();
                                string intLast = dr_Indentity["increment_value"].ToString();

                                string strColumnName_Indentity = dr_Indentity["COLUMNNAME"].ToString();
                                if (strColumnName.Trim() == strColumnName_Indentity.Trim())
                                {
                                    strCreateTable = strCreateTable + " AUTO_INCREMENT";
                                }
                            }
                            strCreateTable = strCreateTable + " ,";

                        }
                        #endregion
                        

                        if (TableColumn_PrimaryKey.Rows.Count == 0)
                            strCreateTable = strCreateTable.Substring(0, strCreateTable.Length - 1);
                        else
                        {
                            string strColumnPrimaryKey = "";
                            foreach (DataRow dr_PrimaryKey in TableColumn_PrimaryKey.Rows)
                            {
                                strColumnPrimaryKey += dr_PrimaryKey["PrimaryKey"].ToString() + ",";
                            }
                            strCreateTable = strCreateTable + " primary key (" + strColumnPrimaryKey.Substring(0, strColumnPrimaryKey.Length - 1) + ")";

                        }
                        strCreateTable = strCreateTable + ")";

                        //check bang nay co trong database hay chua 
                        if (clsExecuteQuery.CheckTableExist(MySql_con, MySql_con.Database.ToString(), strTableName))
                        {
                            // delete truoc khi muon tao
                            clsExecuteQuery.DropTable(MySql_con, strTableName);
                        }
                        // create table
                        clsExecuteQuery.ExecuteMySqlQuery(MySql_con, strCreateTable);
                        // Insert Data for table
                        #region Insert Data
                        foreach (DataRow dr in _DataTableCurrent.Rows)
                        {
                            int Paramater = 0;
                            string strValue = "";
                            string fullValues = "";
                            foreach (var item in dr.ItemArray) // Loop over the items.
                            {
                                Paramater = Paramater + 1;

                                if (Common.CheckIsNumber(item.ToString()))
                                {
                                    if (item.ToString().Length == 0)
                                    {
                                        strValue += item + "(NULL)" + ",";
                                    }
                                    else
                                    {
                                        strValue += item + ",";
                                    }
                                }
                                else
                                {
                                    if (item.ToString().Length == 0)
                                    {
                                        strValue += item + "(NULL)" + ",";
                                    }
                                    else
                                    {
                                        if (Common.CheckIsDateTime(item.ToString()))
                                        {
                                            strValue += "'" + Convert.ToDateTime(item.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "',";
                                        }
                                        else
                                        {
                                            if (item.ToString().ToLower() == "true")
                                            {
                                                strValue += item.ToString() + ",";
                                            }
                                            else if (item.ToString().ToLower() == "false")
                                            {
                                                strValue += item.ToString() + ",";
                                            }
                                            else
                                            {
                                                strValue += "'" + item.ToString().Replace("'", "''") + "',";
                                            }
                                            
                                        }
                                    } 
                                }

                                
                            }
                            fullValues = strValue.Substring(0, strValue.Length - 1);
                            string strInsert = "INSERT INTO " + strTableName + " VALUES (" + fullValues + ")";
                            clsExecuteQuery.ExecuteMySqlQuery(MySql_con, strInsert);
                        }
                        #endregion
                        #endregion
                   }
                    #region add table relationship
                        string strCreateRelationship = "";
                        foreach (DataRow dr_Relationship in _RelationshipTable.Rows)
                        {
                            strCreateRelationship = "ALTER TABLE " + dr_Relationship["Parent table"].ToString();
                            strCreateRelationship = strCreateRelationship + " ADD FOREIGN KEY ( `" + dr_Relationship["name"].ToString() + "` )";
                            strCreateRelationship = strCreateRelationship + " REFERENCES " + dr_Relationship["Refrenced table"].ToString() + " (`" + dr_Relationship["Refrenced column"].ToString() + "`)";
                            clsExecuteQuery.ExecuteMySqlQuery(MySql_con, strCreateRelationship);
                        }
                    #endregion



                }
                else
                {
                    #region Create table SQL
                    //// get thong tin cua bang
                    //DataTable InformationTable = new DataTable();
                    //InformationTable = clsExecuteQuery.InformationTable(sql_con, strTableName);
                    ////get column khoa chinh
                    //DataTable TableColumn_PrimaryKey = new DataTable();
                    //TableColumn_PrimaryKey = clsExecuteQuery.ColumnPrimaryKey(sql_con, strTableName);
                    ////get cot tang tu dong
                    //DataTable TableColumn_Indentity = new DataTable();
                    //TableColumn_Indentity = clsExecuteQuery.ColumnIdentity(sql_con, strTableName);

                    //string strCreateTable = "Create table " + strTableName + " ( ";

                    //foreach (DataRow dr in InformationTable.Rows)
                    //{
                    //    string strColumnName = dr["COLUMN_NAME"].ToString();
                    //    string strValueDefault = dr["COLUMN_DEFAULT"].ToString();
                    //    string strDataType = string.Empty;
                    //    string strCanNull = dr["IS_NULLABLE"].ToString();

                    //    //get xem co cho null hay ko ?------------------------
                    //    if (strCanNull == "NO")
                    //        strCanNull = "Not Null";
                    //    else
                    //        strCanNull = string.Empty;
                    //    //Get maxlength------------------------
                    //    if (Common.IsNullOrEmpty(dr["CHARACTER_MAXIMUM_LENGTH"]))
                    //        strDataType = dr["DATA_TYPE"].ToString();
                    //    else
                    //    {
                    //        if (dr["DATA_TYPE"].ToString() == "ntext")
                    //            strDataType = dr["DATA_TYPE"].ToString();
                    //        else
                    //            strDataType = dr["DATA_TYPE"].ToString() + "(" + dr["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
                    //    }
                    //    //get values default------------------------
                    //    if (!Common.IsNullOrEmpty(strValueDefault))
                    //        strValueDefault = " Default N'" + strValueDefault + "'";
                    //    strCreateTable = strCreateTable + strColumnName + " " + strDataType + " " + strCanNull + " " + strValueDefault;

                    //    foreach (DataRow dr_Indentity in TableColumn_Indentity.Rows)
                    //    {
                    //        string intFirst = dr_Indentity["SEED_VALUE"].ToString();
                    //        string intLast = dr_Indentity["increment_value"].ToString();

                    //        string strColumnName_Indentity = dr_Indentity["COLUMNNAME"].ToString();
                    //        if (strColumnName.Trim() == strColumnName_Indentity.Trim())
                    //        {
                    //            strCreateTable = strCreateTable + " identity(" + intFirst + "," + intLast + ")";
                    //        }
                    //    }
                    //    strCreateTable = strCreateTable + " ,";

                    //}
                    //if (TableColumn_PrimaryKey.Rows.Count == 0)
                    //    strCreateTable = strCreateTable.Substring(0, strCreateTable.Length - 1);
                    //else
                    //{
                    //    string strColumnPrimaryKey = "";
                    //    foreach (DataRow dr_PrimaryKey in TableColumn_PrimaryKey.Rows)
                    //    {
                    //        strColumnPrimaryKey += dr_PrimaryKey["PrimaryKey"].ToString() + ",";
                    //    }
                    //    strCreateTable = strCreateTable + " primary key (" + strColumnPrimaryKey.Substring(0, strColumnPrimaryKey.Length - 1) + ")";

                    //}
                    //strCreateTable = strCreateTable + ")";
                    //sql_con.Close();


                    //get Relationship giua cac table
                    DataTable _RelationshipTable = new DataTable();
                    _RelationshipTable = clsExecuteQuery.getRelationshipMy_Sql(sql_con);

                    //get all table
                    string selectTablesName_sql = "SELECT TABLE_NAME FROM information_schema.tables";
                    DataTable _listTable = new DataTable();
                    _listTable = clsExecuteQuery.SelectSql_ReturnTable(sql_con, selectTablesName_sql);
                    foreach (DataRow dr_listTable in _listTable.Rows)
                    {
                        //string strTableName = "tbl_Messages";
                        string strTableName = dr_listTable["TABLE_NAME"].ToString().Trim();
                        strTableError = strTableName;
                        #region Create table MySQL
                        //get Data
                        string strSelect = "Select * from " + strTableName;
                        DataTable _DataTableCurrent = new DataTable();
                        _DataTableCurrent = clsExecuteQuery.SelectSql_ReturnTable(sql_con, strSelect);

                        // get thong tin cua bang
                        DataTable InformationTable = new DataTable();
                        InformationTable = clsExecuteQuery.InformationTable(sql_con, strTableName);
                        //get column khoa chinh
                        DataTable TableColumn_PrimaryKey = new DataTable();
                        TableColumn_PrimaryKey = clsExecuteQuery.ColumnPrimaryKey(sql_con, strTableName);
                        //get cot tang tu dong
                        DataTable TableColumn_Indentity = new DataTable();
                        TableColumn_Indentity = clsExecuteQuery.ColumnIdentity(sql_con, strTableName);


                        #region Create table
                        string strCreateTable = "Create table " + strTableName + " ( ";
                        foreach (DataRow dr in InformationTable.Rows)
                        {
                            string strColumnName = "`" + dr["COLUMN_NAME"].ToString() + "`";
                            string strValueDefault = dr["COLUMN_DEFAULT"].ToString();
                            string strDataType = string.Empty;
                            string strCanNull = dr["IS_NULLABLE"].ToString();

                            //get xem co cho null hay ko ?------------------------
                            if (strCanNull == "NO")
                                strCanNull = "Not Null";
                            else
                                strCanNull = string.Empty;
                            //Get maxlength------------------------
                            if (Common.IsNullOrEmpty(dr["CHARACTER_MAXIMUM_LENGTH"]))
                                strDataType = dr["DATA_TYPE"].ToString();
                            else
                            {
                                if (dr["DATA_TYPE"].ToString() == "ntext")
                                    strDataType = "text CHARACTER SET utf8 COLLATE utf8_general_ci";
                                else
                                {
                                    decimal _maxLength = 0;
                                    if (Common.ParseDecimal(dr["CHARACTER_MAXIMUM_LENGTH"].ToString()) < 0)
                                    {
                                        _maxLength = 300;
                                    }
                                    else
                                    {
                                        _maxLength = Common.ParseDecimal(dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
                                    }

                                    strDataType = dr["DATA_TYPE"].ToString() + "(" + _maxLength + ")";
                                }
                            }
                            //get values default------------------------
                            if (!Common.IsNullOrEmpty(strValueDefault))
                                strValueDefault = " Default N'" + strValueDefault + "'";
                            strCreateTable = strCreateTable + strColumnName + " " + strDataType + " " + strCanNull + " " + strValueDefault;

                            foreach (DataRow dr_Indentity in TableColumn_Indentity.Rows)
                            {
                                string intFirst = dr_Indentity["SEED_VALUE"].ToString();
                                string intLast = dr_Indentity["increment_value"].ToString();

                                string strColumnName_Indentity = dr_Indentity["COLUMNNAME"].ToString();
                                if (strColumnName.Trim() == strColumnName_Indentity.Trim())
                                {
                                    strCreateTable = strCreateTable + " AUTO_INCREMENT";
                                }
                            }
                            strCreateTable = strCreateTable + " ,";

                        }
                        #endregion


                        if (TableColumn_PrimaryKey.Rows.Count == 0)
                            strCreateTable = strCreateTable.Substring(0, strCreateTable.Length - 1);
                        else
                        {
                            string strColumnPrimaryKey = "";
                            foreach (DataRow dr_PrimaryKey in TableColumn_PrimaryKey.Rows)
                            {
                                strColumnPrimaryKey += dr_PrimaryKey["PrimaryKey"].ToString() + ",";
                            }
                            strCreateTable = strCreateTable + " primary key (" + strColumnPrimaryKey.Substring(0, strColumnPrimaryKey.Length - 1) + ")";

                        }
                        strCreateTable = strCreateTable + ")";

                        //check bang nay co trong database hay chua 
                        if (clsExecuteQuery.CheckTableExist(MySql_con, MySql_con.Database.ToString(), strTableName))
                        {
                            // delete truoc khi muon tao
                            clsExecuteQuery.DropTable(MySql_con, strTableName);
                        }
                        // create table
                        clsExecuteQuery.ExecuteMySqlQuery(MySql_con, strCreateTable);
                        // Insert Data for table
                        #region Insert Data
                        foreach (DataRow dr in _DataTableCurrent.Rows)
                        {
                            int Paramater = 0;
                            string strValue = "";
                            string fullValues = "";
                            foreach (var item in dr.ItemArray) // Loop over the items.
                            {
                                Paramater = Paramater + 1;

                                if (Common.CheckIsNumber(item.ToString()))
                                {
                                    if (item.ToString().Length == 0)
                                    {
                                        strValue += item + "(NULL)" + ",";
                                    }
                                    else
                                    {
                                        strValue += item + ",";
                                    }
                                }
                                else
                                {
                                    if (item.ToString().Length == 0)
                                    {
                                        strValue += item + "(NULL)" + ",";
                                    }
                                    else
                                    {
                                        if (Common.CheckIsDateTime(item.ToString()))
                                        {
                                            strValue += "'" + Convert.ToDateTime(item.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "',";
                                        }
                                        else
                                        {
                                            strValue += "'" + item.ToString().Replace("'", "''") + "',";
                                        }
                                    }
                                }


                            }
                            fullValues = strValue.Substring(0, strValue.Length - 1);
                            string strInsert = "INSERT INTO " + strTableName + " VALUES (" + fullValues + ")";
                            clsExecuteQuery.ExecuteMySqlQuery(MySql_con, strInsert);
                        }
                        #endregion
                        #endregion
                    }
                    #region add table relationship
                    string strCreateRelationship = "";
                    foreach (DataRow dr_Relationship in _RelationshipTable.Rows)
                    {
                        strCreateRelationship = "ALTER TABLE " + dr_Relationship["Parent table"].ToString();
                        strCreateRelationship = strCreateRelationship + " ADD FOREIGN KEY ( `" + dr_Relationship["name"].ToString() + "` )";
                        strCreateRelationship = strCreateRelationship + " REFERENCES " + dr_Relationship["Refrenced table"].ToString() + " (`" + dr_Relationship["Refrenced column"].ToString() + "`)";
                        clsExecuteQuery.ExecuteMySqlQuery(MySql_con, strCreateRelationship);
                    }
                    #endregion


                    #endregion
                }





                #region MyRegion
                //////string tableName = lkSql_tablesName.Text.Trim();
                ////////SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_city'
                //////SetConnection();
                //////sql_con.Open();
                //////sql_cmd = sql_con.CreateCommand();
                //////string selectTypeDataColumns = "SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "'";

                //////DataTable dtTablesName = new DataTable();
                //////sql_Ad = new SqlDataAdapter(selectTypeDataColumns, sql_con);
                //////DS.Reset();
                //////sql_Ad.Fill(DS);
                //////dtTablesName = DS.Tables[0];
                //////string fullValues = "";
                //////#region Foreach Data Type of table
                //////foreach (DataRow dr in dtTablesName.Rows)
                //////    {
                //////        int Paramater = 0;
                //////        string strValue = "";
                //////        string TypeData = "";
                //////        foreach (var item in dr.ItemArray) // Loop over the items.
                //////        {
                //////            Paramater = Paramater + 1;
                //////            if (Paramater == 1)
                //////            { strValue += item + " "; }

                //////            if (Paramater == 2)
                //////            {
                //////                if (item.ToString().ToLower() == "int")
                //////                {
                //////                    TypeData = item.ToString().ToLower();
                //////                    strValue += " " + item + "";
                //////                }
                //////                else {
                //////                    TypeData = item.ToString().ToLower();
                //////                    strValue += " " + item + "";
                //////                }

                //////            }
                //////            if (Paramater == 3)
                //////            {
                //////                if (TypeData == "int")
                //////                {
                //////                    strValue += "(10),";
                //////                }
                //////                else if (TypeData == "nvarchar")
                //////                {
                //////                    if (item.ToString() == "-1")
                //////                    {
                //////                        strValue += "(1000),";
                //////                    }
                //////                    else {
                //////                        strValue += "(" + item + "),";
                //////                    }
                //////                }
                //////                else if (TypeData == "bit")
                //////                {
                //////                    strValue += "(64),";
                //////                }
                //////                else
                //////                {
                //////                    strValue += "(" + item + "),";
                //////                }
                //////            }


                //////        }
                //////        fullValues += strValue;
                //////    }

                //////#endregion
                //////string codeCreateTable = "CREATE TABLE " + tableName + " (" + fullValues.Substring(0, fullValues.Length - 1) + " );";
                //////ExecuteMySqlQuery(codeCreateTable);
                //////sql_con.Close();
                #endregion


            }
            catch (Exception)
            {

                throw;
            }
        }
    }
}
